const express = require('express');
const router = express.Router();

/**
 * 菜品分页条件查询
 * GET /dishes
 */
router.get('/', async (req, res) => {
  try {
    const { 
      name, 
      categoryIds, 
      tasteIds, 
      difficulty, 
      cookTime, 
      page = 1, 
      pageSize = 10 
    } = req.query;
    
    // 转换参数类型
    const pageNum = parseInt(page);
    const pageSizeNum = parseInt(pageSize);
    const offset = (pageNum - 1) * pageSizeNum;
    
    // 构建基础查询
    let sql = `
      SELECT DISTINCT d.dish_id, d.name, d.difficulty, d.cook_time, d.created_at
      FROM dishes d
    `;
    
    // 添加连接条件
    const joins = [];
    const whereConditions = [];
    const params = [];
    
    // 分类过滤
    if (categoryIds && categoryIds.length > 0) {
      const categoryIdsArray = Array.isArray(categoryIds) ? categoryIds : [categoryIds];
      joins.push('JOIN dish_categories dc ON d.dish_id = dc.dish_id');
      whereConditions.push('dc.category_id IN (?)');
      params.push(categoryIdsArray);
    }
    
    // 口味过滤
    if (tasteIds && tasteIds.length > 0) {
      const tasteIdsArray = Array.isArray(tasteIds) ? tasteIds : [tasteIds];
      joins.push('JOIN dish_tastes dt ON d.dish_id = dt.dish_id');
      whereConditions.push('dt.taste_id IN (?)');
      params.push(tasteIdsArray);
    }
    
    // 名称模糊搜索
    if (name) {
      whereConditions.push('d.name LIKE ?');
      params.push(`%${name}%`);
    }
    
    // 难度过滤
    if (difficulty) {
      whereConditions.push('d.difficulty = ?');
      params.push(difficulty);
    }
    
    // 时长过滤
    if (cookTime) {
      whereConditions.push('d.cook_time = ?');
      params.push(cookTime);
    }
    
    // 组合SQL
    if (joins.length > 0) {
      sql += ' ' + joins.join(' ');
    }
    
    if (whereConditions.length > 0) {
      sql += ' WHERE ' + whereConditions.join(' AND ');
    }
    
    // 计算总数
    const countSql = `SELECT COUNT(DISTINCT d.dish_id) as total FROM dishes d ${joins.join(' ')} ${whereConditions.length > 0 ? 'WHERE ' + whereConditions.join(' AND ') : ''}`;
    const [countResult] = await req.db.query(countSql, params);
    const total = countResult[0].total;
    
    // 分页和排序
    sql += ' ORDER BY d.created_at DESC LIMIT ? OFFSET ?';
    params.push(pageSizeNum, offset);
    
    // 执行查询
    const [rows] = await req.db.query(sql, params);
    
    // 获取每个菜品的分类、口味和主图
    const dishIds = rows.map(row => row.dish_id);
    
    // 如果没有菜品，直接返回空结果
    if (dishIds.length === 0) {
      return res.json({
        code: 1,
        msg: 'success',
        data: {
          data: [],
          pageSize: pageSizeNum,
          pageNumber: pageNum,
          total: 0
        }
      });
    }
    
    // 查询分类
    const [categories] = await req.db.query(
      `SELECT dc.dish_id, c.category_name 
       FROM dish_categories dc 
       JOIN categories c ON dc.category_id = c.category_id 
       WHERE dc.dish_id IN (?)`,
      [dishIds]
    );
    
    // 查询口味
    const [tastes] = await req.db.query(
      `SELECT dt.dish_id, t.taste_name 
       FROM dish_tastes dt 
       JOIN tastes t ON dt.taste_id = t.taste_id 
       WHERE dt.dish_id IN (?)`,
      [dishIds]
    );
    
    // 查询主图
    const [images] = await req.db.query(
      `SELECT dish_id, image_path 
       FROM images 
       WHERE dish_id IN (?) AND step_id IS NULL 
       ORDER BY uploaded_at ASC`,
      [dishIds]
    );
    
    // 组装结果
    const dishMap = {};
    rows.forEach(dish => {
      dishMap[dish.dish_id] = {
        dishId: dish.dish_id,
        name: dish.name,
        difficulty: dish.difficulty,
        cook_time: dish.cook_time,
        categories: [],
        tastes: [],
        mainImage: null
      };
    });
    
    // 添加分类
    categories.forEach(cat => {
      if (dishMap[cat.dish_id]) {
        dishMap[cat.dish_id].categories.push(cat.category_name);
      }
    });
    
    // 添加口味
    tastes.forEach(taste => {
      if (dishMap[taste.dish_id]) {
        dishMap[taste.dish_id].tastes.push(taste.taste_name);
      }
    });
    
    // 添加主图
    images.forEach(img => {
      if (dishMap[img.dish_id] && !dishMap[img.dish_id].mainImage) {
        dishMap[img.dish_id].mainImage = img.image_path;
      }
    });
    
    // 转换为数组
    const result = Object.values(dishMap);
    
    res.json({
      code: 1,
      msg: 'success',
      data: {
        data: result,
        pageSize: pageSizeNum,
        pageNumber: pageNum,
        total
      }
    });
  } catch (error) {
    console.error('查询菜品列表出错:', error);
    res.status(500).json({
      code: 0,
      msg: '查询菜品列表失败',
      data: null
    });
  }
});

/**
 * 菜品详情查询
 * GET /dishes/:id
 */
router.get('/:id', async (req, res) => {
  try {
    const dishId = parseInt(req.params.id);
    
    // 查询菜品基本信息
    const [dishes] = await req.db.query(
      'SELECT dish_id, name, difficulty, cook_time FROM dishes WHERE dish_id = ?',
      [dishId]
    );
    
    if (dishes.length === 0) {
      return res.status(404).json({
        code: 0,
        msg: '菜品不存在',
        data: null
      });
    }
    
    const dish = dishes[0];
    
    // 查询分类
    const [categories] = await req.db.query(
      `SELECT c.category_name 
       FROM dish_categories dc 
       JOIN categories c ON dc.category_id = c.category_id 
       WHERE dc.dish_id = ?`,
      [dishId]
    );
    
    // 查询口味
    const [tastes] = await req.db.query(
      `SELECT t.taste_name 
       FROM dish_tastes dt 
       JOIN tastes t ON dt.taste_id = t.taste_id 
       WHERE dt.dish_id = ?`,
      [dishId]
    );
    
    // 查询步骤
    const [steps] = await req.db.query(
      `SELECT s.step_id, s.step_name, s.sequence, s.description, s.duration_min, i.image_path 
       FROM steps s 
       LEFT JOIN images i ON s.step_id = i.step_id 
       WHERE s.dish_id = ? 
       ORDER BY s.sequence ASC`,
      [dishId]
    );
    
    // 查询菜品图片
    const [images] = await req.db.query(
      `SELECT image_path 
       FROM images 
       WHERE dish_id = ? AND step_id IS NULL`,
      [dishId]
    );
    
    // 组装结果
    const result = {
      dishId: dish.dish_id,
      name: dish.name,
      difficulty: dish.difficulty,
      cookTime: dish.cookTime,
      categories: categories.map(c => c.category_name),
      tastes: tastes.map(t => t.taste_name),
      steps: steps.map(s => ({
        stepId: s.step_id,
        stepName: s.step_name,
        sequence: s.sequence,
        description: s.description,
        durationMin: s.duration_min,
        image: s.image_path
      })),
      images: images.map(img => img.image_path)
    };
    
    res.json({
      code: 1,
      msg: 'success',
      data: result
    });
  } catch (error) {
    console.error('查询菜品详情出错:', error);
    res.status(500).json({
      code: 0,
      msg: '查询菜品详情失败',
      data: null
    });
  }
});

/**
 * 新增菜品
 * POST /dishes
 */
router.post('/', async (req, res) => {
  const connection = await req.db.getConnection();
  
  try {
    await connection.beginTransaction();
    
    const { name, difficulty, cookTime, categoryIds, tasteIds, steps, images } = req.body;
    
    // 1. 插入菜品基本信息
    const [result] = await connection.query(
      'INSERT INTO dishes (name, difficulty, cook_time) VALUES (?, ?, ?)',
      [name, difficulty, cookTime]
    );
    
    const dishId = result.insertId;
    
    // 2. 关联分类
    if (categoryIds && categoryIds.length > 0) {
      const categoryValues = categoryIds.map(categoryId => [dishId, categoryId]);
      await connection.query(
        'INSERT INTO dish_categories (dish_id, category_id) VALUES ?',
        [categoryValues]
      );
    }
    
    // 3. 关联口味
    if (tasteIds && tasteIds.length > 0) {
      const tasteValues = tasteIds.map(tasteId => [dishId, tasteId]);
      await connection.query(
        'INSERT INTO dish_tastes (dish_id, taste_id) VALUES ?',
        [tasteValues]
      );
    }
    
    // 4. 添加步骤
    if (steps && steps.length > 0) {
      for (const step of steps) {
        const [stepResult] = await connection.query(
          'INSERT INTO steps (dish_id, step_name, sequence, description, duration_min) VALUES (?, ?, ?, ?, ?)',
          [dishId, step.stepName, step.sequence, step.description, step.durationMin]
        );
      }
    }
    
    // 5. 添加图片
    if (images && images.length > 0) {
      const imageValues = images.map(imagePath => [dishId, null, imagePath]);
      await connection.query(
        'INSERT INTO images (dish_id, step_id, image_path) VALUES ?',
        [imageValues]
      );
    }
    
    await connection.commit();
    
    res.status(201).json({
      code: 1,
      msg: 'success',
      data: {
        dishId
      }
    });
  } catch (error) {
    await connection.rollback();
    console.error('新增菜品出错:', error);
    res.status(500).json({
      code: 0,
      msg: '新增菜品失败',
      data: null
    });
  } finally {
    connection.release();
  }
});

/**
 * 修改菜品
 * PUT /dishes/:id
 */
router.put('/:id', async (req, res) => {
  const connection = await req.db.getConnection();
  
  try {
    await connection.beginTransaction();
    
    const dishId = parseInt(req.params.id);
    const { name, difficulty, cookTime, categoryIds, tasteIds, steps, images } = req.body;
    
    // 检查菜品是否存在
    const [dishes] = await connection.query(
      'SELECT dish_id FROM dishes WHERE dish_id = ?',
      [dishId]
    );
    
    if (dishes.length === 0) {
      await connection.rollback();
      return res.status(404).json({
        code: 0,
        msg: '菜品不存在',
        data: null
      });
    }
    
    // 1. 更新菜品基本信息
    const updateFields = [];
    const updateParams = [];
    
    if (name !== undefined) {
      updateFields.push('name = ?');
      updateParams.push(name);
    }
    
    if (difficulty !== undefined) {
      updateFields.push('difficulty = ?');
      updateParams.push(difficulty);
    }
    
    if (cookTime !== undefined) {
      updateFields.push('cook_time = ?');
      updateParams.push(cookTime);
    }
    
    if (updateFields.length > 0) {
      updateParams.push(dishId);
      await connection.query(
        `UPDATE dishes SET ${updateFields.join(', ')} WHERE dish_id = ?`,
        updateParams
      );
    }
    
    // 2. 更新分类关联
    if (categoryIds !== undefined) {
      // 删除旧关联
      await connection.query(
        'DELETE FROM dish_categories WHERE dish_id = ?',
        [dishId]
      );
      
      // 添加新关联
      if (categoryIds && categoryIds.length > 0) {
        const categoryValues = categoryIds.map(categoryId => [dishId, categoryId]);
        await connection.query(
          'INSERT INTO dish_categories (dish_id, category_id) VALUES ?',
          [categoryValues]
        );
      }
    }
    
    // 3. 更新口味关联
    if (tasteIds !== undefined) {
      // 删除旧关联
      await connection.query(
        'DELETE FROM dish_tastes WHERE dish_id = ?',
        [dishId]
      );
      
      // 添加新关联
      if (tasteIds && tasteIds.length > 0) {
        const tasteValues = tasteIds.map(tasteId => [dishId, tasteId]);
        await connection.query(
          'INSERT INTO dish_tastes (dish_id, taste_id) VALUES ?',
          [tasteValues]
        );
      }
    }
    
    // 4. 更新步骤
    if (steps !== undefined) {
      for (const step of steps) {
        if (step.stepId) {
          // 更新现有步骤
          await connection.query(
            'UPDATE steps SET step_name = ?, sequence = ?, description = ?, duration_min = ? WHERE step_id = ? AND dish_id = ?',
            [step.stepName, step.sequence, step.description, step.durationMin, step.stepId, dishId]
          );
        } else {
          // 添加新步骤
          await connection.query(
            'INSERT INTO steps (dish_id, step_name, sequence, description, duration_min) VALUES (?, ?, ?, ?, ?)',
            [dishId, step.stepName, step.sequence, step.description, step.durationMin]
          );
        }
      }
    }
    
    // 5. 更新图片
    if (images !== undefined) {
      // 删除旧图片
      await connection.query(
        'DELETE FROM images WHERE dish_id = ? AND step_id IS NULL',
        [dishId]
      );
      
      // 添加新图片
      if (images && images.length > 0) {
        const imageValues = images.map(imagePath => [dishId, null, imagePath]);
        await connection.query(
          'INSERT INTO images (dish_id, step_id, image_path) VALUES ?',
          [imageValues]
        );
      }
    }
    
    await connection.commit();
    
    res.json({
      code: 1,
      msg: 'success',
      data: null
    });
  } catch (error) {
    await connection.rollback();
    console.error('修改菜品出错:', error);
    res.status(500).json({
      code: 0,
      msg: '修改菜品失败',
      data: null
    });
  } finally {
    connection.release();
  }
});

/**
 * 删除菜品
 * DELETE /dishes/:id
 */
router.delete('/:id', async (req, res) => {
  try {
    const dishId = parseInt(req.params.id);
    
    // 检查菜品是否存在
    const [dishes] = await req.db.query(
      'SELECT dish_id FROM dishes WHERE dish_id = ?',
      [dishId]
    );
    
    if (dishes.length === 0) {
      return res.status(404).json({
        code: 0,
        msg: '菜品不存在',
        data: null
      });
    }
    
    // 删除菜品（级联删除会自动删除关联数据）
    await req.db.query(
      'DELETE FROM dishes WHERE dish_id = ?',
      [dishId]
    );
    
    res.json({
      code: 1,
      msg: 'success',
      data: null
    });
  } catch (error) {
    console.error('删除菜品出错:', error);
    res.status(500).json({
      code: 0,
      msg: '删除菜品失败',
      data: null
    });
  }
});

module.exports = router;